/**
 * @author Mie Plougstrup, Anders Abildgaard, Bo Stokholm 
 * @Version 16-12-2014
 */

package dbLayer;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import modelLayer.BusinessPartner;
import modelLayer.Customer;
import modelLayer.Supplier;

public class DbBusinessPartner {

	Connection con;
	DbAddress dbAddress;

	/**
	 * Constructor - Initializes the fields.
	 */
	public DbBusinessPartner() {
		con = DbConnection.getInstance().getDBcon();
		dbAddress = new DbAddress();
	}

	/**
	 * Method for retrieving a customer by its id.
	 * 
	 * @param int customerId
	 * @return Customer object
	 */
	public Customer retrieveCustomerById(int customerId) {
		return (Customer) singleWhere("partnerId =" + customerId, true);
	}

	public ArrayList<Customer> retrieveCustomerByName(String attValue) {
		String wClause = " businessName like '%" + attValue + "%' AND partnerType = 0";
		ArrayList<BusinessPartner> bList = multiWhere(wClause, true);
		ArrayList<Customer> cList = new ArrayList<Customer>();
		for(BusinessPartner b: bList) {
			cList.add((Customer) b);
		}
		return cList;
	}

	/**
	 * Method for retrieving the names of all drivers, with approximately the same name.
	 * 
	 * @param text
	 * @return
	 */
	public ArrayList<String> retrieveSupNameListByString(String text) {
		ArrayList<String> matches = new ArrayList<String>();
		ResultSet results;
		String query = "Select businessName from businessPartner where businessPartner.partnertype = 1 AND businessPartner.businessName like '%" + text + "%'";
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			while(results.next()) {
				matches.add(results.getString("businessName"));
			}
			stmt.close();

		} catch(Exception e) {
			e.printStackTrace();
		}

		return matches;
	}

	/**
	 * Method for retrieving a supplier by its id.
	 * 
	 * @param int supplierId
	 * @return Supplier object
	 */
	public Supplier retrieveSupplierById(int supplierId) {
		return (Supplier) singleWhere("partnerId =" + supplierId, true);
	}

	/**
	 * Retrieve a supplier by name
	 * @param String attValue
	 * @return Supplier Object
	 */
	public Supplier retrieveSupplierByName(String attValue){
		String wClause = " businessName like '%" + attValue + "%'";
		return (Supplier) singleWhere(wClause, true);
	}

	/**
	 * Retrieve a supplier by phone.
	 * @param String attValue
	 * @return Supplier object
	 */
	public Supplier retrieveByPhone(String attValue){
		String wClause = " phoneNo like '%" + attValue + "%'";
		return (Supplier) singleWhere(wClause, true);
	}

	public boolean phoneNoUsed(String phone) {
		String wClause = " phoneNo = '" + phone + "'";
		if(singleWhere(wClause, false) != null) {
			return true;
		} else {
			return false;
		}
	}

	public boolean nameUsed(String name) {
		String wClause = " businessName = '" + name + "'";
		if(singleWhere(wClause, false) != null) {
			return true;
		} else {
			return false;
		}
	}


	/**
	 * Method for inserting a customer object into the database.
	 * 
	 * @param Customer cObj
	 * @return int ra
	 * @throws Exception 
	 */
	public int insertCustomer(Customer cObj) throws Exception {
		int ra = -1;

		String query = "Insert into businessPartner(partnerId, businessName, phoneNo, partnerType)"
				+ "Values(?,?,?,?)";
		try {
			PreparedStatement prpstmt = con.prepareStatement(query);
			prpstmt.setInt(1, cObj.getPartnerId());
			prpstmt.setString(2, cObj.getBusinessName());
			prpstmt.setString(3, cObj.getPhoneNo());
			prpstmt.setInt(4, 0);
			prpstmt.setQueryTimeout(5);
			ra = prpstmt.executeUpdate();
			prpstmt.close();
		} catch(Exception e) {
			throw new Exception("Customer not inserted correct");
		}
		return ra;
	}

	/**
	 * Method for inserting a supplier into the database.
	 * 
	 * @param Supplier sObj
	 * @return int ra
	 * @throws Exception
	 */
	public int insertSupplier(Supplier sObj) throws Exception {
		int ra = -1;

		String query = "Insert into businessPartner(partnerId, businessName, phoneNo, partnerType)"
				+ "Values(?,?,?,?)";
		try {
			PreparedStatement prpstmt = con.prepareStatement(query);
			prpstmt.setInt(1, sObj.getPartnerId());
			prpstmt.setString(2, sObj.getBusinessName());
			prpstmt.setString(3, sObj.getPhoneNo());
			prpstmt.setInt(4, 1);
			prpstmt.setQueryTimeout(5);
			ra = prpstmt.executeUpdate();
			prpstmt.close();
		} catch(Exception e) {
			throw new Exception("Supplier not inserted correct");
		}
		return ra;
	}

	/**
	 * Method for updating a customer in the database.
	 * 
	 * @param Customer cObj
	 * @return int ra
	 */
	public int updateCustomer(Customer cObj) {
		int ra = -1;

		String query = "Update BusinessPartner SET " +
				"businessName ='" + cObj.getBusinessName()
				+ "', phoneNo ='" + cObj.getPhoneNo() +
				"' WHERE partnerId =" + cObj.getPartnerId();
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			ra = stmt.executeUpdate(query);
			stmt.close();
		} catch(Exception e) {}
		return ra;
	}

	/**
	 * Method for updating a supplier in the database.
	 * 
	 * @param Supplier sObj
	 * @return int ra
	 */
	public int updateSupplier(Supplier sObj) {
		int ra = -1;

		String query = "Update BusinessPartner SET " +
				"businessName ='" + sObj.getBusinessName()
				+ "', phoneNo ='" + sObj.getPhoneNo() +
				"' WHERE partnerId =" + sObj.getPartnerId();
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			ra = stmt.executeUpdate(query);
			stmt.close();
		} catch(Exception e) {}
		return ra;
	}

	/**
	 * Method for deleting a businesspartner object from the database.
	 * 
	 * @param BusinessPartner bpObj
	 * @return int ra
	 */
	public int delete(BusinessPartner bpObj) {
		int ra = -1;

		String query = "DELETE FROM BusinessPartner WHERE partnerId =" + bpObj.getPartnerId();
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			ra = stmt.executeUpdate(query);
			stmt.close();
		} catch(Exception e) {}
		return ra;
	}

	/**
	 * Method that creates a single businessPartner object, based on a where clause.
	 * 
	 * @param String wClause
	 * @param boolean retrieveAssociation
	 * @return BusinessPartner object
	 */
	private BusinessPartner singleWhere(String wClause, boolean retrieveAssociation) {
		ResultSet results;
		BusinessPartner bpObj = null;

		String query = buildQuery(wClause);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			if(results.next()) {
				if(results.getInt("partnerType") == 0) {
					bpObj = buildCustomer(results);
					if(retrieveAssociation) {
						Customer cObj = (Customer) bpObj;
						cObj.setAdList(dbAddress.retrieveAdListForCustomer(cObj.getPartnerId()));
						stmt.close();
						return cObj;
					}
				} else if(results.getInt("partnerType") == 1) {
					bpObj = buildSupplier(results);
					if(retrieveAssociation) {
						Supplier sObj = (Supplier) bpObj;
						sObj.setAddress(dbAddress.retrieveForSupplier(sObj.getPartnerId()));
						stmt.close();
						return sObj;
					}
				}
				stmt.close();
			}
		} catch(Exception e) {}
		return bpObj;
	}

	/**
	 * Method that creates multiple businessPartner objects based on a where clause.
	 * 
	 * @param String wClause
	 * @param boolean retrieveAssociation
	 * @return ArrayList<BusinessPartner> list
	 */
	private ArrayList<BusinessPartner> multiWhere(String wClause, boolean retrieveAssociation){
		ResultSet results;
		ArrayList<BusinessPartner> bpList = new ArrayList<BusinessPartner>();

		String query = buildQuery(wClause);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			while(results.next()) {
				if(results.getInt("partnerType") == 0) {
					Customer cObj = buildCustomer(results);
					cObj.setAdList(dbAddress.retrieveAdListForCustomer(cObj.getPartnerId()));
					bpList.add(cObj);
				} else if(results.getInt("partnerType") == 1) {
					Supplier sObj = buildSupplier(results);
					sObj.setAddress(dbAddress.retrieveForSupplier(sObj.getPartnerId()));
					bpList.add(sObj);
				}
			}//While end
			stmt.close();
		} catch(Exception e) {}
		return bpList;
	}

	/**
	 * Method that builds a where clause, used for database queries.
	 * 
	 * @param String wClause
	 * @return String query
	 */
	private String buildQuery(String wClause) {
		String query = "SELECT partnerId, businessName, phoneNo, partnerType from BusinessPartner";

		if(wClause.length() > 0) {
			query = query + " WHERE " + wClause;
		}
		return query;
	}

	/**
	 * Method for building a customer object.
	 * 
	 * @param ResultSet results
	 * @return Customer object
	 */
	private Customer buildCustomer(ResultSet results) {
		Customer cObj = new Customer();
		cObj = (Customer) setValues(cObj, results);
		return cObj;
	}

	/**
	 * Method for building a supplier object.
	 * 
	 * @param ResultSet results
	 * @return Supplier object
	 */
	private Supplier buildSupplier(ResultSet results) {
		Supplier sObj = new Supplier();
		sObj = (Supplier) setValues(sObj, results);
		return sObj;
	}

	/**
	 * Method for setting the common values of a businesspartner object.
	 * 
	 * @param BusinessPartner bpObj
	 * @param ResultSet results
	 * @return BusinessPartner object
	 */
	private BusinessPartner setValues(BusinessPartner bpObj, ResultSet results) {
		try {
			bpObj.setPartnerId(results.getInt("partnerId"));
			bpObj.setBusinessName(results.getString("businessName"));
			bpObj.setPhoneNo(results.getString("phoneNo"));
		} catch(Exception e) {}
		return bpObj;
	}




}
